﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Model;
using Automobile.service.Common;
using System.Data.SqlClient;
using System.Data;
using System.Web.Script.Serialization;
namespace Automobile.service.DAL
{
  public  class CaigouDAL
    {
      SQLHelper helper = new SQLHelper();
      /// <summary>
      /// 根据年份区间获取采购信息
      /// </summary>
      /// <param name="firstyear"></param>
      /// <param name="lastyear"></param>
      /// <returns></returns>
      public string CaigouByyear(string firstyear,string lastyear)
      {
          StringBuilder builder = new StringBuilder("select year(Purchase_Date) Purchase_Date,sum(SumMoney) SumMoney from Purchase where YEAR(Purchase_Date) between " + firstyear + "  and " + lastyear + " group by year(Purchase_Date)");
          DataTable table = helper.ExecDataTable(builder.ToString());
          JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
          List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
          Dictionary<string, object> childRow;
          foreach (DataRow row in table.Rows)
          {
              childRow = new Dictionary<string, object>();
              foreach (DataColumn col in table.Columns)
              {
                  childRow.Add(col.ColumnName, row[col]);
              }
              parentRow.Add(childRow);
          }
          return jsSerializer.Serialize(parentRow);
      }

      /// <summary>
      /// 根据月份获取采购信息
      /// </summary>
      /// <param name="firstyear"></param>
      /// <param name="lastyear"></param>
      /// <returns></returns>
      public string CaigouByMonth(string firstyear, string firstmonth,string lastmonth)
      {
          StringBuilder builder = new StringBuilder("select month(Purchase_Date) Purchase_Date,sum(SumMoney) SumMoney from Purchase where month(Purchase_Date) between " + firstmonth + "  and " + lastmonth + "  group by month(Purchase_Date),year(Purchase_Date)  having year(Purchase_Date)='" + firstyear + "'");
          DataTable table = helper.ExecDataTable(builder.ToString());
          JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
          List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
          Dictionary<string, object> childRow;
          foreach (DataRow row in table.Rows)
          {
              childRow = new Dictionary<string, object>();
              foreach (DataColumn col in table.Columns)
              {
                  childRow.Add(col.ColumnName, row[col]);
              }
              parentRow.Add(childRow);
          }
          return jsSerializer.Serialize(parentRow);
      }
    }
}
